package jcolibri.test.recomendador.datos;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import jcolibri.cbrcore.CBRCase;
import jcolibri.test.recomendador.MovieIdent;
import jcolibri.test.recomendador.UserIdent;
import jcolibri.test.recomendador.datos.User.Gender;
import jcolibri.test.recomendador.datos.User.Occupation;

public class AccesoBBDD {
	private ExtendedExtendedDatabaseConnector _connector;
	
	public AccesoBBDD (ExtendedExtendedDatabaseConnector c){
		_connector=c;
	}
	
	public boolean existeUsuario (Integer s){
		try{
			ResultSet rs;
			String cadena = "SELECT * FROM user where ident="+s;
			 rs = _connector.executeSQLQuery(cadena);
			 if (rs.next())
				return true;
			 else
				return false;
		}catch (Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public int maximoIdDeMovie(){
		int maxId=-1;;
		try{
			ResultSet rs;
        	 rs=_connector.executeSQLQuery("SELECT MAX (ident) FROM movie");
			 if (rs.next()) maxId=rs.getInt(1);
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return maxId;
	}	
	
	public Movie devuelvePeliculaAlAzar(int maximo){
		Movie m=null;
		try{
			ResultSet rs;
			 int numeroAleatorio = (int) (Math.random()*maximo+1);
			 rs = _connector.executeSQLQuery("SELECT * FROM movie where ident="+numeroAleatorio);
			 if (rs.next()){
				m=pasaDeResultSetAMovie(rs);
			 }else{
				 m.setTitle("NO SE HA ENCONTRAO LA PELI EN LA BBDD. ERROR!!");
			 }
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return m;
	}
	
	public Movie devuelvePelicula(int idMovie){
		Movie m=null;
		try{
			ResultSet rs;
			 rs = _connector.executeSQLQuery("SELECT * FROM movie where ident="+idMovie);
			 if (rs.next()){
				m=pasaDeResultSetAMovie(rs);
			 }else{
				 m.setTitle("NO SE HA ENCONTRAO LA PELI EN LA BBDD. ERROR!!");
			 }
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return m;
	}
	
	
	public ArrayList<Movie> obtenerPeliculas(Collection<CBRCase> cases){
		ArrayList<Movie> a=new ArrayList<Movie>();	
		Iterator<CBRCase> i=cases.iterator();
		while (i.hasNext()){
			CBRCase c=i.next();
			MovieIdent movieId=(MovieIdent)c.getSolution();
			try{
				ResultSet rs;
				 rs = _connector.executeSQLQuery("SELECT * FROM movie where ident="+movieId.getId2());
				 Movie m=new Movie();
				 if (rs.next()){
					m=pasaDeResultSetAMovie(rs);
				 }else{
					 m.setTitle("NO SE HA ENCONTRAO LA PELI EN LA BBDD. ERROR!!");
				 }
				 a.add(m);
			}catch (Exception e){
				System.out.println(e.getMessage());
			}
		}
		return a;
	}
	
	public Collection<CBRCase> invertirDescriptionYSolution(Collection<CBRCase> cases){
		ArrayList<CBRCase> a=new ArrayList<CBRCase>();	
		Iterator<CBRCase> i=cases.iterator();
		CBRCase c=new CBRCase() ;
		if (i.hasNext()) c=i.next();
		UserIdent u=(UserIdent)c.getDescription();
		ArrayList<Integer> pelisYaRateadas=new ArrayList<Integer>();
		try{
			ResultSet rs;
			 rs = _connector.executeSQLQuery("SELECT movieId FROM rating where userId="+u.getId());
			 while (rs.next())
				 pelisYaRateadas.add(rs.getInt(1));
		}catch(Exception e){
			e.printStackTrace();
		}
		while (i.hasNext()){
			MovieIdent movieId=(MovieIdent)c.getSolution();
			if (!pelisYaRateadas.contains(movieId.getId2()))
			{
			try{
				ResultSet rs;
				 rs = _connector.executeSQLQuery("SELECT * FROM movie where ident="+movieId.getId2());
				 Movie m=new Movie();
				 if (rs.next()){
					m=pasaDeResultSetAMovie(rs);
				 }else{
					 m.setTitle("NO SE HA ENCONTRAO LA PELI EN LA BBDD. ERROR!!");
				 }
				 CBRCase caso=new CBRCase();
				 caso.setDescription(m);
				 a.add(caso);
			}catch (Exception e){
				System.out.println(e.getMessage());
			}
			}
			c=i.next();
		}
		return a;
	}

	public boolean ratearPeliculas(ArrayList<Rateo> ratings){
		int i=0;
		int MaxIdent=-1;
		boolean noProblem=true;
		try{
			ResultSet rs = _connector.executeSQLQuery("SELECT MAX(ident) FROM rating");
			if (rs.next())
				MaxIdent=rs.getInt(1);
			else
				noProblem=false;
		}catch (Exception e){
			System.out.println(e.getMessage());
			noProblem=false;
		}
		while (i < ratings.size() && noProblem){
			Rateo r=ratings.get(i);
			i=i+1;
			try{
				ResultSet rs = _connector.executeSQLQuery("SELECT * FROM rating where userId="+r.getIdUser()+" and movieId="+r.getIdMovie());
				if (rs.next())
					System.out.println("Pelicula ya rateada por este usario, pasando del rateo");
				else
					_connector.executeSQLUpdate("INSERT INTO rating VALUES ("+r.getIdUser() +"," +r.getIdMovie()+
						","+r.getRating()+","+(MaxIdent+i)+")");
			}catch(Exception e){
				System.out.println(e.getMessage());
				noProblem=false;
			}
		}
		return noProblem;
		
	}
	
	public int maximoIdDeUsuario(){
		int maxId=-1;;
		try{
			ResultSet rs;
        	 rs=_connector.executeSQLQuery("SELECT MAX (ident) FROM user");
			 if (rs.next()) maxId=rs.getInt(1);
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return maxId;
	}	
	
	public int altaUsuario(User u){
		int nuevoId=maximoIdDeUsuario()+1;
		try{
    		_connector.executeSQLUpdate("INSERT INTO user VALUES('"+nuevoId+"" +
    		   "','"+u.getAge()+"','"+u.getGender()+"','"+u.getOccupation()+"','"+u.getZipCode()+"')");
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		return nuevoId;
	}
	
	public void creaVistaRating(){
		try{
			_connector.executeSQLUpdate("DROP VIEW IF EXISTS vistaRating");
			_connector.executeSQLUpdate("CREATE VIEW vistaRating  AS (SELECT * FROM RATING)");
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}

	//Recibe Movie solo para indicarle con qu� generos debe hacer la vista
	//Es decir, solo nos interesan los flags de generos de M
	public void creaVistaRating(Movie m){
		try{
			_connector.executeSQLUpdate("DROP VIEW IF EXISTS vistaRating");
			_connector.executeSQLUpdate("CREATE VIEW vistaRating  AS (" +
		    		"SELECT RATING.* FROM RATING,MOVIE WHERE " +
		    		"RATING.movieId=MOVIE.Ident AND " +
		    		"MOVIE.genreAnimation=1)");
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}
	
	public User devuelveUsuario (Integer s){
		User u;
		try{
			ResultSet rs;
			String cadena = "SELECT * FROM user where ident="+s;
			 rs = _connector.executeSQLQuery(cadena);
			 if (rs.next())
				u=pasaDeResultSetAUser(rs);
			 else
				u=null;
		}catch (Exception e){
			System.out.println(e.getMessage());
			return null;
		}
		return u;
	}
	
	public User pasaDeResultSetAUser(ResultSet rs){
		User u=new User();
		try{
		u.setId(rs.getInt("ident"));
		u.setAge(rs.getInt("age"));
		u.setGender(Gender.valueOf(rs.getString("gender")));
		u.setOccupation(Occupation.valueOf(rs.getString("occupation")));
		u.setZipCode(rs.getString("zipCode"));
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return u;
	}
	
	public Movie pasaDeResultSetAMovie(ResultSet rs){
		Movie m=new Movie();
		try{
		m.setId(rs.getInt("ident"));
		m.setTitle(rs.getString("title"));
		m.setReleaseDate(rs.getString("releaseDate"));
		m.setVideoReleaseDate(rs.getString("videoReleaseDate"));
		m.setURL(rs.getString("url"));
		m.setGenreUnknown(rs.getBoolean("genreUnknown"));
		m.setGenreAction(rs.getBoolean("genreAction"));
		m.setGenreAdventure(rs.getBoolean("genreAdventure"));
		m.setGenreAnimation(rs.getBoolean("genreAnimation"));
		m.setGenreChildren(rs.getBoolean("genreChildren"));
		m.setGenreComedy(rs.getBoolean("genreComedy"));
		m.setGenreCrime(rs.getBoolean("genreCrime"));
		m.setGenreDocumentary(rs.getBoolean("genreDocumentary"));
		m.setGenreDrama(rs.getBoolean("genreDrama"));
		m.setGenreFantasy(rs.getBoolean("genreFantasy"));
		m.setGenreFilmNoir(rs.getBoolean("genreFilmNoir"));
		m.setGenreHorror(rs.getBoolean("genreHorror"));
		m.setGenreMusical(rs.getBoolean("genreMusical"));
		m.setGenreMystery(rs.getBoolean("genreMistery"));
		m.setGenreRomance(rs.getBoolean("genreRomance"));
		m.setGenreSciFi(rs.getBoolean("genreSciFi"));
		m.setGenreThriller(rs.getBoolean("genreThriller"));
		m.setGenreWar(rs.getBoolean("genreWar"));
		m.setGenreWestern(rs.getBoolean("genreWestern"));
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return m;
	}
	
}
